﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using Excel;
using Host.Models;

namespace Host.Controllers
{
    public class DataController : Controller
    {
        #region Constant
        #region Variables
        private const string DownloadFolder = "~/App_Data/uploads";//Download location
        #endregion

        #region Models
        FileModel _fileModel = new FileModel();//Model used for the controller
        #endregion
        #endregion

        #region View Callers
        /// <summary>
        /// Request to get the view for uploading data into the system
        /// This view is controlled by the Developer roles and the Administrator roles
        /// </summary>
        /// <returns>DataImport View </returns>
         [Authorize(Roles = "Administrator, Developer")]
        public ActionResult DataImport()
        {
            return View();//Return the view
        }

        /// <summary>
        /// Action request to return the Mapping view
        /// this view allows the mapping of the columns on the excell sheet to questions in in the system
        /// This view is controlled by the Developer and the administration roles in the system.
        /// </summary>
        /// <returns>Map View</returns>
        [Authorize(Roles = "Administrator, Developer")]
        public ActionResult Map()
        {
            return View();//Returns the Map view
        }

        #endregion


        #region Custom Methods and Actions
        
        // This action handles the form POST and the upload
        /// <summary>
        /// Datas the import.
        /// </summary>
        /// <param name="file">The file.</param>
        /// <param name="fund"></param>
        /// <param name="employer"></param>
        /// <returns></returns>
        [HttpPost]
        [Authorize(Roles = "Administrator, Developer")]
        public ActionResult DataImport(HttpPostedFileBase file, string fund, string employer)
        {
            if (file != null && file.ContentLength > 0)// Verify that the user selected a file
            {

                IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(file.InputStream);
                DataSet uploadedExcellSet = excelReader.AsDataSet();

                #region Save the File to disk
                //var fileName = Path.GetFileName(file.FileName); // extract only the fielname
                //var path = Path.Combine(Server.MapPath(DownloadFolder), fileName);// store the file inside ~/App_Data/uploads folder
                //file.SaveAs(path);//Save the file

                #endregion

                #region Create the stream of the file

                //FileStream stream = System.IO.File.Open(path, FileMode.Open, FileAccess.Read);
                    //Open the file and create the stream

                #endregion

                #region Get the bytes for the file

                byte[] bytes = new byte[(int)file.InputStream.Length]; //Create the buffer needed to for the stream
                file.InputStream.Read(bytes, 0, (int)file.InputStream.Length); //While reading put the bytes into the buffer

                #endregion

                #region Close the File
                file.InputStream.Close();
                #endregion

                #region Create the Model
                _fileModel = new FileModel{ Data = uploadedExcellSet, FileName = file.FileName, FundId = Convert.ToInt32(fund), };
                #endregion

                #region Save and  Process the file

                if (new FileHelper().SaveFile(_fileModel, bytes)) //Save the file into the database
                {
                    new FileHelper().ProcessFile(_fileModel); //Validate Records in the file
                }

                #endregion

                #region Old Code

                //    IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
                //    DataSet result = excelReader.AsDataSet();
                //    var members = result.Tables["Members"].AsEnumerable().Select(x => x);
                //    var q = result.Tables["Claims"].AsEnumerable()
                //                .Select(x => new ClaimImportModel
                //                {
                //                            GroupType = x["Column1"].ToString().Trim(),
                //                            FundCode = x["Column2"].ToString().Trim(),
                //                            Surname = x["Column3"].ToString().Trim(),
                //                            Initials = x["Column4"].ToString().Trim(),
                //                            Title = x["Column5"].ToString().Trim(),
                //                            ClaimNo = x["Column23"].ToString().Trim(),
                //                            ClaimDate = x["Column6"].ToString().Trim(),
                //                            TargetUser = x["Column7"].ToString().Trim(),
                //                            AddressLine1 = x["Column8"].ToString().Trim(),
                //                            AddressLine2 = x["Column9"].ToString().Trim(),
                //                            AddressLine3 = x["Column10"].ToString().Trim(),
                //                            PostalCode = x["Column11"].ToString().Trim(),
                //                            FirstNames = x["Column12"].ToString().Trim(),
                //                            TaxRefNo = x["Column13"].ToString().Trim(),
                //                            IdentityNo = x["Column14"].ToString().Trim(),
                //                            BankName = x["Column15"].ToString().Trim(),
                //                            BankAccountNo = x["Column16"].ToString().Trim(),
                //                            BranchCode = x["Column17"].ToString().Trim(),
                //                            AccountType = x["Column18"].ToString().Trim(),
                //                            EmployeeCode = x["Column19"].ToString().Trim(),
                //                            HomeLoan = x["Column20"].ToString().Trim(),
                //                            WithdrawalReinvestFlag = x["Column21"].ToString().Trim(),
                //                            ClaimAmount = x["Column22"].ToString().Trim()
                //                }).ToList();
                //    var dataOnly = q.Where(i => i.AccountType != "AccountType").ToList();
                //    //ExceptionHelper.ValidateClaims(dataOnly);
                //    DAL.Persistance.Claim.ClaimRepository.Submit(dataOnly.Select(x=> new DAL.Persistance.Claim.ClaimRepository
                //                                                                         {
                //                                                                              GroupType = x.GroupType,
                //                                                                                FundCode = x.FundCode,
                //                                                                                Surname = x.Surname,
                //                                                                                Initials = x.Initials,
                //                                                                                Title = x.Title,
                //                                                                                ClaimNo = x.ClaimNo,
                //                                                                                ClaimDate = x.ClaimDate, 
                //                                                                                TargetUser = x.TargetUser,
                //                                                                                AddressLine1 = x.AddressLine1,
                //                                                                                AddressLine2 = x.AddressLine2,
                //                                                                                AddressLine3 = x.AddressLine3,
                //                                                                                PostalCode  = x.PostalCode,
                //                                                                                FirstNames = x.FirstNames,
                //                                                                                TaxRefNo = x.TaxRefNo,
                //                                                                                IdentityNo = x.IdentityNo,
                //                                                                                BankName = x.BankName,
                //                                                                                BankAccountNo = x.BankAccountNo,
                //                                                                                BranchCode = x.BranchCode,
                //                                                                                AccountType = x.AccountType,
                //                                                                                EmployeeCode = x.EmployeeCode,
                //                                                                                HomeLoan = x.HomeLoan,
                //                                                                                WithdrawalReinvestFlag = x.WithdrawalReinvestFlag,
                //                                                                                ClaimAmount = x.ClaimAmount
                //                                                                         }).ToList());
                //}


                // redirect back to the index action to show the form once again
                // return RedirectToAction("DataImport");

                #endregion
            }
            return View(_fileModel);//Returns the View DataImport with the current model for the view
        }

        /// <summary>
        /// Get the effect periods based on the FundId
        /// will filter the employer table with fundId and return employername and the effective period
        /// </summary>
        /// <param name="data">List of Fund objects</param>
        /// <returns>JSON object of EmployerName and EffectivePeriod</returns>
        
        public ActionResult GetEffectivePeriod(List<Models.Fund> data )
        {
            int fundId = data[0].FundId;
            JsonResult result = new JsonResult();//Create the json holder object that will be sent back
            result.Data =
                new Data().Employer.Where(i => i.FundId == fundId).Select(x => new Employer { EmployerName = x.EmployerName, EffectivePeriod = x.EffectivePeriod }).ToList();//Get the employers from the employers table based on the fundId
            return Json(result, JsonRequestBehavior.AllowGet);//Return the JSON object and set the property to the return to allow the get from the client
        }

        #region Old Code
        
        //private void ValidateSet(DataTable data, int sheetId, int fundId)
        //{
        //    for (int col = 0; col < data.Columns.Count; col++)//Loop through the columns
        //    {
        //        for (int row = 1; row < data.Rows.Count; row++)//Loop through the rows
        //        {
        //            //Check if there is a question for the column
        //            if (CheckIfHasQuestion(sheetId, col))
        //            {
        //                Map map = GetMap(sheetId, col);
        //                if (map.QuestionGroupId != null)
        //                    Validate(data.Rows[row][col].ToString(),map.QuestionId,(int) map.QuestionGroupId, fundId , sheetId);
        //            }
        //        }
        //    }
        //}


        //private static bool CheckIfHasQuestion(int sheetId, int mapColumnId)
        //{
        //    var records = new DataModel().Get(mapColumnId, sheetId);
        //    if (records.Count() > 0)
        //    {
        //        return true;
        //    }
        //    return false;
        //}

        //private static Map GetMap(int sheetId, int mapColumnId)
        //{
        //    return new DataModel().Get(mapColumnId, sheetId).Select(x => new Map
        //                                                                     {
        //                                                                         QuestionId = x.questionId,
        //                                                                         QuestionGroupId = x.questionGroupId
        //                                                                     }).Single();
        //}

        ////private void Validate(string value, int questionId, int questionGroupId, int fundId, int sheetId)
        ////{
        ////    //Get Question
        ////    List<DAL.Question> q = new DAL.RCEntities().Questions.Where(i => i.QuestionId == questionId).ToList();
        ////    foreach (var question in q)
        ////    {
        ////        Question questionLocal = question;
        ////        DAL.AnswerDataType dt =
        ////        new DAL.RCEntities().AnswerDataTypes.Where(i => i.AnswerDataTypeId == questionLocal.AnswerDataTypeId).Single();
        ////        DAL.FundAnswerSheet fs =
        ////            new DAL.RCEntities().FundAnswerSheets.Where(i => i.FundId == fundId && i.QuestionId == questionLocal.QuestionId).
        ////                Single();
        ////        switch (dt.AnswerDataTypeDescription)
        ////        {
        ////            case "Boolean":
        ////                try
        ////                {
        ////                    bool answer = Convert.ToBoolean(fs.AnswerValue);
        ////                    bool questionValue = Convert.ToBoolean(value);
        ////                    if (answer != questionValue)
        ////                    {
        ////                        CreateRecordException(questionLocal.QuestionId, sheetId);
        ////                    }
        ////                }
        ////                catch (System.Exception ex)
        ////                {
        ////                    CreateFileException(ex.Message, sheetId);
        ////                }
        ////                break;
        ////            case "String":
        ////                try
        ////                {
        ////                    if (fs.AnswerValue != value)
        ////                    {
        ////                        CreateRecordException(questionLocal.QuestionId, sheetId);
        ////                    }
        ////                }
        ////                catch (System.Exception ex)
        ////                {
        ////                    CreateFileException(ex.Message, sheetId);
        ////                }
        ////                break;
        ////            case "Integer":
        ////                try
        ////                {
        ////                    int intAnswer = Convert.ToInt32(fs.AnswerValue);
        ////                    int intQuestionValue = Convert.ToInt32(value);
        ////                    if (intAnswer != intQuestionValue)
        ////                    {
        ////                        CreateRecordException(questionLocal.QuestionId, sheetId);
        ////                    }
        ////                }
        ////                catch (System.Exception ex)
        ////                {
        ////                    CreateFileException(ex.Message, sheetId);
        ////                }
        ////                break;
        ////            case "Numeric":
        ////                try
        ////                {
        ////                    Decimal decAnswer = Convert.ToDecimal(fs.AnswerValue);
        ////                    Decimal decQuestionValue = Convert.ToDecimal(value);
        ////                    if (decAnswer != decQuestionValue)
        ////                    {
        ////                        CreateRecordException(questionLocal.QuestionId, sheetId);
        ////                    }
        ////                }
        ////                catch (System.Exception ex)
        ////                {
        ////                    CreateFileException(ex.Message, sheetId);
        ////                }
        ////                break;
        ////        }
        ////    }
        ////}

        //private static void CreateRecordException(int questionId, int sheetId)
        //{
        //    //Get Exception based on question and sheet
        //    int fileLogId = Convert.ToInt32(System.Web.HttpContext.Current.Session["fileLogId"]);
        //    int exceptionId =
        //        new DAL.RCEntities().Exceptions.Where(i => i.SheetId == sheetId && i.QuestionId == questionId).Single().
        //            ExceptionId;
        //    FileExceptionLog log = new FileExceptionLog();
        //    switch (sheetId)
        //    {
        //        case 1:
        //            //Claims
        //            ExceptionHelper.CreateException(0, "Exception string", 1);
        //            break;
        //        case 2:
        //            //Members
        //            ExceptionHelper.CreateException(0, "Exception string", 2);
        //            break;
        //        case 3:
        //            //Employers
        //            ExceptionHelper.CreateException(0, "Exception string", 3);
        //            break;
        //        case 4:
        //            //Transactions
        //            ExceptionHelper.CreateException(0, "Exception string", 4);
        //            break;
        //        case 5:
        //            ExceptionHelper.CreateException(0, "Exception string", 5);
        //            break;
        //        case 6:
        //            ExceptionHelper.CreateException(0, "Exception string", 6);
        //            break;
        //        case 7:
        //            ExceptionHelper.CreateException(0, "Exception string", 7);
        //            break;
        //        case 8:
        //            ExceptionHelper.CreateException(0, "Exception string", 8);
        //            break;
        //        case 9:
        //            ExceptionHelper.CreateException(0, "Exception string", 9);
        //            break;
        //        case 10:
        //            ExceptionHelper.CreateException(0, "Exception string", 10);
        //            break;
        //    }

        //}

        //private static void CreateFileException(string exceptionMessage, int sheetId)
        //{

        //}

        #endregion

        [HttpPost]
        public ActionResult CreateMap(List<Map> data)
        {
            Map map = data[0];
            string SheetName;
            switch (map.MapCatagoryId)
            {
                case 1:
                    SheetName = "Claims";
                    break;
                case 2:
                    SheetName = "Members";
                    break;
                case 3:
                    SheetName = "Employers";
                    break;
                case 4:
                    SheetName = "Transactions";
                    break;
                default:
                    SheetName = "Unknown";
                    break;
            }

            using (Models.Data db = new Data())
            {
                db.Map.AddObject(new Models.Map
                                     {
                                         map1 = Convert.ToInt32(map.Column),
                                         mapCatagoryId = Convert.ToInt32(map.MapCatagoryId),
                                         questionId = Convert.ToInt32(map.QuestionId),
                                         questionGroupId = Convert.ToInt32(map.QuestionGroupId),
                                         sheetName = SheetName
                                     });
                db.SaveChanges();
            }
//            new DAL.Persistance.QA.Map().Save(map);//
            return null;
        }

        public ActionResult GetMap(List<Map> data)
        {
            JsonResult result = new JsonResult();
            using (Models.Data db = new Data())
            {
                Map map = data[0];
                var q = db.Map.Where(i => i.mapCatagoryId == map.MapCatagoryId).Select(x => x).ToList();
                List<Map> list = new List<Map>();
                foreach (var m in q)
                {
                    list.Add(new Map
                                 { 
                                     MapId = m.mapId,
                                     MapCatagoryId = m.mapCatagoryId,
                                     Column = m.map1,
                                     QuestionId = m.questionId,
                                     QuestionGroupId = m.questionGroupId,
                                     QuestionDescription = db.Question.Where(i=>i.QuestionId == m.questionId).Select(x=> x).Single().Question1,
                                     QuestionGroupDescription = db.QuestionGroup.Where(i=> i.QuestionGroupId == m.questionGroupId).Select(x=> x).Single().Description
                                 });
                }
                result.Data = list;
            }
            return Json(result, JsonRequestBehavior.AllowGet);
        }

        public JsonResult GetQuestions(List<QuestionGroup> questionGroup)
        {
            var id = Convert.ToInt32(questionGroup[0].QuestionGroupId);
            JsonResult result = new JsonResult();
            result.Data = new Models.Data().Question.Where(i => i.QuestionGroupId == id).Select(x => new Question
                                                                                                             {
                                                                                                                 QuestionId = x.QuestionId,
                                                                                                                 QuestionDescription = x.Question1
                                                                                                                 
                                                                                                             }).ToList();
            return Json(result, JsonRequestBehavior.AllowGet);
           
        }

        public JsonResult GetQuestionGroups()
        {
            JsonResult result = new JsonResult();
            result.Data = new Data().QuestionGroup.Select(x=> new QuestionGroup
                                                                                               {
                                                                                                   QuestionGroupId = x.QuestionGroupId,
                                                                                                   QuestionGroupDescription = x.Description
                                                                                               });
            return Json(result, JsonRequestBehavior.AllowGet);
        }


        public ActionResult DeleteMap(List<Map> data)
        {
            Map map = data[0];
            using (Data db = new Data())
            {
                var query = db.Map.Where(i => i.mapId == map.MapId).Select(x => x).Single();
                db.Map.DeleteObject(query);
                db.SaveChanges();
            }
            return null;
        }

        #endregion
    }

    public class Question
    {
        public int QuestionId { get; set; }

        public int QuestionGroupId { get; set; }

        public string QuestionDescription { get; set; }


        public List<QuestionGroup> GetQuestionGroups()
        {
            
            

            return new Data().QuestionGroup.Select(x => new QuestionGroup
                                                            {
                                                                QuestionGroupDescription = x.Description,
                                                                QuestionGroupId = x.QuestionGroupId
                                                            }).ToList();
        }

        public Question GetQuestion(int questionId)
        {
            return new Data().Question.Where(i => i.QuestionId == questionId).Select(x => new Question
            {
                QuestionDescription = x.Question1,
                QuestionId = x.QuestionId
            }).Single();
        }
        public QuestionGroup GetQuestionGroup(int questionGroupId)
        {
            return
                new Data().QuestionGroup.Where(i => i.QuestionGroupId == questionGroupId).Select(x => new QuestionGroup
                                                                                                          {
                                                                                                              QuestionGroupDescription = x.Description,
                                                                                                              QuestionGroupId = x.QuestionGroupId
                                                                                                          }).Single();
        }
    }

    public class Employer
    {
        public string EmployerName { get; set; }
        public string EffectivePeriod { get; set; }
    }

    public class Map
    {
        public int MapId { get; set; }
        public int MapCatagoryId { get; set; }
        public int Column { get; set; }
        public string QuestionDescription { get; set; }
        public int QuestionId { get; set; }
        public string QuestionGroupDescription { get; set; }
        public int? QuestionGroupId { get; set; }
    }



    public class QuestionGroup
    {
        public int QuestionGroupId { get; set; }

        public string QuestionGroupDescription { get; set; }
    }
}
